﻿using System;
using System.Data;
using TopBuilds.Database;

namespace TopBuilds.Restaurant.Data
{
    public class ProductObject
    {
        public static Product[] GetProducts()
        {
            return Query(null);
        }

        public static Product GetProduct(int id)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.ConditionAdd(ProductDesc.Id, ConditionExpress.EQUALS, id);

            Product[] set = Query(builder.ConditionString);

            if (set != null && set.Length > 0)
            {
                return set[0];
            }

            return null;
        }

        public static Product GetProduct(string name)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.ConditionAdd(ProductDesc.Name, ConditionExpress.EQUALS, name);

            Product[] set = Query(builder.ConditionString);

            if (set != null && set.Length > 0)
            {
                return set[0];
            }

            return null;
        }


        public static Product[] Query(string condition)
        {

            // query factories from database
            string sql = "select * from " + ProductDesc.TableName;
            if (condition != null && condition.Length > 0)
            {
                sql += " where " + condition;
            }
            DataTable table = DatabaseObject.Instance.ExecuteQuerySQL(sql);


            // rebuild the data
            if (table != null && table.Rows.Count > 0)
            {
                Product[] products = new Product[table.Rows.Count];

                for (int i = 0; i < table.Rows.Count; i++)
                {
					Product product = new Product();

                    product.Id                            = Convert.ToInt32(table.Rows[i][ProductDesc.Id]);
                    product.ProductCategoryId             = Convert.ToInt32(table.Rows[i][ProductDesc.ProductCategoryId]);
                    product.ProductSetId                  = Convert.ToInt32(table.Rows[i][ProductDesc.ProductSetId]);
                    product.UserDefinedId                 = Convert.ToString(table.Rows[i][ProductDesc.UserDefinedId]);
                    product.Name                          = Convert.ToString(table.Rows[i][ProductDesc.Name]);
                    product.Price                         = Convert.ToSingle(table.Rows[i][ProductDesc.Price]);
                    product.SpecialPrice                  = Convert.ToSingle(table.Rows[i][ProductDesc.SpecialPrice]);

                    Object o 							  = table.Rows[i][ProductDesc.DiscountOptionId];
                    product.DiscountOptionId              = (o == DBNull.Value) ? 0 : Convert.ToInt32(o);

                    o = table.Rows[i][ProductDesc.SalePromotionId];
                    product.SalePromotionId 			  = (o == DBNull.Value) ? 0 : Convert.ToInt32(o);

                    product.IsSet                         = Convert.ToInt32(table.Rows[i][ProductDesc.IsSet]);
                    product.AllowBillDiscount             = Convert.ToInt32(table.Rows[i][ProductDesc.AllowBillDiscount]);

                    products.SetValue(product, i);
                }

                return products;
            }
            return null;
        }

        public static Int32 Add(Product product)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.InsertAdd(ProductDesc.ProductCategoryId,           product.ProductCategoryId);
            builder.InsertAdd(ProductDesc.ProductSetId,                product.ProductSetId);
            builder.InsertAdd(ProductDesc.UserDefinedId,               product.UserDefinedId);
            builder.InsertAdd(ProductDesc.Name,                        product.Name);
            builder.InsertAdd(ProductDesc.Price,                       product.Price);
            builder.InsertAdd(ProductDesc.SpecialPrice,                product.SpecialPrice);
            if (product.DiscountOptionId > 0)
            {
                builder.InsertAdd(ProductDesc.DiscountOptionId, product.DiscountOptionId);
            }
            if (product.SalePromotionId > 0)
            {
                builder.InsertAdd(ProductDesc.SalePromotionId, product.SalePromotionId);
            }
            builder.InsertAdd(ProductDesc.IsSet,                       product.IsSet);
            builder.InsertAdd(ProductDesc.AllowBillDiscount,           product.AllowBillDiscount);
            builder.QueryTableName = ProductDesc.TableName;

            string sql = builder.InsertCommandLine;

            return DatabaseObject.Instance.ExecuteNonQuerySQL(sql);
        }

        public static bool Delete(Int32 id)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.ConditionAdd(ProductDesc.Id, ConditionExpress.EQUALS, id);
            builder.QueryTableName = ProductDesc.TableName;
            string sql = builder.DeleteCommandLine;

            return (DatabaseObject.Instance.ExecuteNonQuerySQL(sql) != -1);
        }

        public static Int32 Update(Product product)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.UpdateAdd(ProductDesc.ProductCategoryId,              product.ProductCategoryId);
            builder.UpdateAdd(ProductDesc.ProductSetId,                   product.ProductSetId);
            builder.UpdateAdd(ProductDesc.UserDefinedId,                  product.UserDefinedId);
            builder.UpdateAdd(ProductDesc.Name,                           product.Name);
            builder.UpdateAdd(ProductDesc.Price,                          product.Price);
            builder.UpdateAdd(ProductDesc.SpecialPrice,                   product.SpecialPrice);
            builder.UpdateAdd(ProductDesc.DiscountOptionId,               product.DiscountOptionId);
            builder.UpdateAdd(ProductDesc.SalePromotionId,                product.SalePromotionId);
            builder.UpdateAdd(ProductDesc.IsSet,                          product.IsSet);
            builder.UpdateAdd(ProductDesc.AllowBillDiscount,              product.AllowBillDiscount);
            builder.ConditionAdd(ProductDesc.Id, ConditionExpress.EQUALS, product.Id);
            builder.QueryTableName = ProductDesc.TableName;

            string sql = builder.UpdateCommandLine;

            return DatabaseObject.Instance.ExecuteNonQuerySQL(sql);
        }

    }
}
